//
//  DataBaseHandler.m
//  UI19_homework
//
//  Created by dllo on 16/1/13.
//  Copyright © 2016年 dllo. All rights reserved.
//

#import "DataBaseHandler.h"
#import <sqlite3.h>
#import "EveryDayModel.h"

@implementation DataBaseHandler

+ (instancetype)shareDataBaseHandler {


    // 在静态区, 只能初始化一次
    static DataBaseHandler *dataBase = nil;
    
    if (dataBase == nil) {
        dataBase = [[DataBaseHandler alloc] init];
    }

    return dataBase;
}

#pragma mark - 打开数据库


// 定义一个 sqlite 结构体(对象)
sqlite3 *db;

- (void)open {
    
    // API: sqlite3_open
    
    if (db != nil) {
        
        NSLog(@"已开启!");
        return;
    }
    
    NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"ProjectB.sqlite"];
    
    // filename 数据库文件的路径(注: 类型为C字符串   ppDb 数据库对象)
    int result = sqlite3_open(file.UTF8String, &db);
    
    if (result == SQLITE_OK) {
        NSLog(@"open !");
    }else{
        
        NSLog(@"NO!, code = %d", result);
    }
}

#pragma mark - 创建表格

- (void)createTable{
    
    // API: sqlite3_exec
    
    // 创建 SQL 语句
    NSString *creatTableSQL = @"CREATE TABLE IF NOT EXISTS lanou1130(number INTEGER PRIMARY KEY AUTOINCREMENT, title text, category text, duration text, feed text, blurred text, descrip text, collectionCount text, shareCount text, replyCount text, playUrl text, BDUrl text)";
    
    int result = sqlite3_exec(db, creatTableSQL.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"OK!!");
    }else{
        
        NSLog(@"CAN NOT! code:%d", result);
    }
}


#pragma mark - 插入数据
- (void)insertModelWriteRead:(EveryDayModel *)model{
    
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT into lanou1130(title, category, duration, feed, blurred, descrip, collectionCount, shareCount, replyCount, playUrl, BDUrl) VALUES('%@','%@','%@','%@','%@','%@','%@','%@','%@','%@','%@')", model.title, model.category,  model.duration, [model.cover objectForKey:@"feed"], [model.cover objectForKey:@"blurred"], model.descrip, [model.consumption objectForKey:@"collectionCount"], [model.consumption objectForKey:@"shareCount"], [model.consumption objectForKey:@"replyCount"], model.playUrl, [[model.playInfo objectAtIndex:0] objectForKey:@"url"]];
    
    
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL,NULL, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"inster OK!");
    }else{
        NSLog(@"Error, code:%d", result);
    }
}


#pragma mark - 更新数据
- (void)updataModel:(EveryDayModel *)model forNumber:(NSInteger)number{

    NSString *updataSQL = [NSString stringWithFormat:@"UPDATE lanou1130 SET title = '%@', category = '%@',  duration = '%@', feed = '%@', blurred = '%@', descrip = '%@', collectionCount = '%@', shareCount = '%@', replyCount = '%@', playUrl = '%@', BDUrl = '%@' WHERE number = '%ld'", model.title, model.category, model.duration, [model.cover objectForKey:@"feed"], [model.cover objectForKey:@"blurred"], model.descrip, [model.consumption objectForKey:@"collectionCount"], [model.consumption objectForKey:@"shareCount"], [model.consumption objectForKey:@"replyCount"], model.playUrl, [[model.playInfo objectAtIndex:0] objectForKey:@"url"], number];
    

    int result = sqlite3_exec(db, updataSQL.UTF8String, NULL, NULL, nil);

    if (result == SQLITE_OK) {

        NSLog(@"up OK!");
    }else{

        NSLog(@"FULL, code:%d", result);
    }
}


- (void)deleteWithTitle:(NSString *) title{
    
    // API: sqlite3_exec
    
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM lanou1130 WHERE title  = '%@'",title];
    int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
        
    }else {
        
        NSLog(@"删除失败, code:%d", result);
    }
    
}

- (NSArray *)selectForTitle:(NSString *) title{
    
    // 存放符合条件的数据(元素是model对象)
    NSMutableArray *arr = [NSMutableArray array];
    
    // sql语句.
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM lanou1130 WHERE title = '%@'",title];
   
    // API: stmt结构体, sqlite3_prepareV2, sqlite3_step, sqlite3_column,
    
    // 声明一个stmt对象(结构体)
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"准备就绪, 查询中....");
        NSLog(@"%d", result);

        // 每行都执行查询语句
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            // 如果查询条件匹配, 通过 sqlite3_column函数簇 取出值.
            
            // 取出那一列的数据值
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            
            const unsigned char *category = sqlite3_column_text(stmt, 2);
            
            const unsigned char *duration = sqlite3_column_text(stmt, 3);
            
            const unsigned char *feed = sqlite3_column_text(stmt, 4);
            
            const unsigned char *blurred = sqlite3_column_text(stmt, 5);
            const unsigned char *descrip = sqlite3_column_text(stmt, 6);
            const unsigned char *collectionCount = sqlite3_column_text(stmt, 7);
            const unsigned char *shareCount = sqlite3_column_text(stmt, 8);
            const unsigned char *replyCount = sqlite3_column_text(stmt, 9);

            const unsigned char *playUrl = sqlite3_column_text(stmt, 10);
            const unsigned char *BDUrl = sqlite3_column_text(stmt, 11);
            
            EveryDayModel *model = [[EveryDayModel alloc] init];
            
            
            model.title = [NSString stringWithUTF8String:(const char *) title];
            
            model.category = [NSString stringWithUTF8String:(const char *) category];
            
            model.duration = [NSString stringWithUTF8String:(const char *) duration];
            
            
            model.cover = [NSMutableDictionary dictionary];
            
            [model.cover setValue:[NSString stringWithUTF8String:(const char *) feed] forKey:@"feed"];
            
            [model.cover setValue:[NSString stringWithUTF8String:(const char *) blurred] forKey:@"blurred"];
            
            model.descrip = [NSString stringWithUTF8String:(const char *) descrip];
            
            model.consumption = [NSMutableDictionary dictionary];
            
            [model.consumption setValue:[NSString stringWithUTF8String:(const char *) collectionCount] forKey:@"collectionCount"];
            
             [model.consumption setValue:[NSString stringWithUTF8String:(const char *) shareCount] forKey:@"shareCount"];
            
             [model.consumption setValue:[NSString stringWithUTF8String:(const char *) replyCount] forKey:@"replyCount"];
            
            model.playUrl = [NSString stringWithUTF8String:(const char *) playUrl];

            
            NSString *str = [NSString stringWithUTF8String:(const char *) BDUrl];
            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
            [dic setValue:str forKey:@"url"];
            
            model.playInfo = [NSMutableArray array];
            
            [model.playInfo addObject:dic];
        
            
            [arr addObject:model];
            
        }
        // 销毁对象.
        sqlite3_finalize(stmt);
        
    }else {
        
        NSLog(@"不能正常查询, code:%d", result);
        
        // 销毁对象
        sqlite3_finalize(stmt);
        
        
    }
    
    return arr;
    
}


- (NSMutableArray *)select{
    // 存放符合条件的数据(元素是model对象)
    NSMutableArray *arr = [NSMutableArray array];
    
    // sql语句.
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM lanou1130 "];
    
    // API: stmt结构体, sqlite3_prepareV2, sqlite3_step, sqlite3_column,
    
    // 声明一个stmt对象(结构体)
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"准备就绪, 查询中....");
          NSLog(@"%@", NSHomeDirectory());
        // 每行都执行查询语句
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            // 如果查询条件匹配, 通过 sqlite3_column函数簇 取出值.
            
            
            // 取出那一列的数据值
            const unsigned char *title = sqlite3_column_text(stmt, 1);
            
            const unsigned char *category = sqlite3_column_text(stmt, 2);
            
            const unsigned char *duration = sqlite3_column_text(stmt, 3);
            
            const unsigned char *feed = sqlite3_column_text(stmt, 4);
            
            const unsigned char *blurred = sqlite3_column_text(stmt, 5);
            const unsigned char *descrip = sqlite3_column_text(stmt, 6);
            const unsigned char *collectionCount = sqlite3_column_text(stmt, 7);
            const unsigned char *shareCount = sqlite3_column_text(stmt, 8);
            const unsigned char *replyCount = sqlite3_column_text(stmt, 9);
            
            const unsigned char *playUrl = sqlite3_column_text(stmt, 10);
            const unsigned char *BDUrl = sqlite3_column_text(stmt, 11);

            // 创建modoel对象, 赋值之后放入数组中.
            EveryDayModel *model = [[EveryDayModel alloc] init];
            
            model.title = [NSString stringWithUTF8String:(const char *) title];
            
            model.category = [NSString stringWithUTF8String:(const char *) category];
            
            model.duration = [NSString stringWithUTF8String:(const char *) duration];
            
            model.cover = [NSMutableDictionary dictionary];
            
            [model.cover setValue:[NSString stringWithUTF8String:(const char *) feed] forKey:@"feed"];
            
            [model.cover setValue:[NSString stringWithUTF8String:(const char *) blurred] forKey:@"blurred"];
            
            model.descrip = [NSString stringWithUTF8String:(const char *) descrip];
            
            model.consumption = [NSMutableDictionary dictionary];
            
            [model.consumption setValue:[NSString stringWithUTF8String:(const char *) collectionCount] forKey:@"collectionCount"];
            
            [model.consumption setValue:[NSString stringWithUTF8String:(const char *) shareCount] forKey:@"shareCount"];
            
            [model.consumption setValue:[NSString stringWithUTF8String:(const char *) replyCount] forKey:@"replyCount"];
            
            model.playUrl = [NSString stringWithUTF8String:(const char *) playUrl];
            
            
            NSString *str = [NSString stringWithUTF8String:(const char *) BDUrl];
            NSMutableDictionary *dic = [NSMutableDictionary dictionary];
            [dic setValue:str forKey:@"url"];
            
            model.playInfo = [NSMutableArray array];
            
            [model.playInfo addObject:dic];
            
            
            [arr addObject:model];
            
        }
        
        // 销毁对象.
        sqlite3_finalize(stmt);
        
    }else {
        
        NSLog(@"不能正常查询, code:%d", result);
        
        // 销毁对象
        sqlite3_finalize(stmt);
        
    }
    
    return arr;
    
}

#pragma mark - 删除表单
- (void)dropTable{
    NSString *dropSQL = @"DROP TABLE lanou1130";
    
    int result = sqlite3_exec(db, dropSQL.UTF8String, nil, nil, nil);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"drop OK!");
    }else{
        
        NSLog(@"FULL, code:%d", result);
    }
    
}

#pragma mark - 关掉数据库
- (void)closeTable{
    
    int result = sqlite3_close(db);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"close OK!");
        
        db = nil;
    }else{
        
        NSLog(@"FULL, code:%d", result);
    }
}



@end
